package dao;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.DbUtil;
import entity.User;

public class UserDao {
	public User login(String account, String pwd) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
	        conn = getConnection();
			ps = conn.prepareStatement("select * from user_new where account=? and pwd=?");
			ps.setObject(1, account);
			ps.setObject(2, pwd);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				User user = new User();
				user.setAccount(rs.getString("account"));
				user.setUserName(rs.getString("user_name"));
				user.setHeadimg(rs.getString("headimg"));
				user.setRole(rs.getInt("role"));
				return user;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeConnection(ps, conn);
		}
		return null;
	}
	public int insert(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
        	conn = getConnection();
            ps = conn.prepareStatement("insert into user_new (account, user_name, pwd, role, headimg, create_time) "
            		+ "values (?,?,?,?,?,?)");
            ps.setObject(1, user.getAccount());
            ps.setObject(2, user.getUserName());
            ps.setObject(3, user.getPwd());
            ps.setObject(4, user.getRole());
            ps.setObject(5, user.getHeadimg());
            ps.setObject(6, new Date());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return -1;
    }

	public Integer total_user(String condition, List<Object> parmas) {
		Connection conn = null;
		PreparedStatement ps = null;
        try {
			conn = getConnection();
			String sql = "select count(*) from user_new where "+ condition;
			ps = conn.prepareStatement(sql);
			for (int i =1;i <= parmas.size();i++){
				ps.setObject(i,parmas.get(i-1));
			}
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
            	return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return 0;
    }

//	public Integer total_user() {
//		Connection conn = null;
//		Statement stmt = null;
//		try {
//			conn = getConnection();
//			stmt = conn.createStatement();
//			String sql = "select count(*) from user_new";
//			ResultSet rs = stmt.executeQuery(sql);
//			if(rs.next()) {
//				return rs.getInt(1);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			closeConnection(stmt, conn);
//		}
//		return 0;
//	}

	public int sure_user(String account) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			String sql = "select count(*) from user_new where account=" + account;
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(stmt, conn);
		}
		return 0;
	}

	public int regist(String account,String userName,String pwd) {
		LocalDateTime currentTime = LocalDateTime.now();
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
		String createTime = currentTime.format(formatter);
		int ret = DbUtil.getInstance().execute("insert into user_new(account,user_name,pwd,role,headimg,create_time) values(?,?,?,?,?,?)", new Object[]{account,userName,pwd,0,"img/hand.jpg",createTime});
		return ret;
	}

	public int regist(String account,String userName,String pwd,Integer role) {
		LocalDateTime currentTime = LocalDateTime.now();
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
		String createTime = currentTime.format(formatter);
		int ret = DbUtil.getInstance().execute("insert into user_new(account,user_name,pwd,role,headimg,create_time) values(?,?,?,?,?,?)", new Object[]{account,userName,pwd,role,"img/hand.jpg",createTime});
		return ret;
	}

//	public List<User> queryUserPage(int start, int size) {
//        Connection conn = null;
//        Statement stmt = null;
//        try {
//        	conn = getConnection();
//            stmt = conn.createStatement();
//            String sql = "select * from user_new limit " + start + ", " + size;
//            ResultSet rs = stmt.executeQuery(sql);
//            List<User> list = new ArrayList<User>();
//            while(rs.next()) {
//            	User user = new User();
//            	user.setId(rs.getInt("id"));
//            	user.setUserName(rs.getString("user_name"));
//            	user.setPwd(rs.getString("pwd"));
//            	user.setRole(rs.getInt("role"));
//            	user.setAccount(rs.getString("account"));
//				user.setHeadimg(rs.getString("headimg"));
//            	if (rs.getTimestamp("create_time") != null) {
//            		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                	user.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
//            	}
//            	list.add(user);
//            }
//            return list;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            closeConnection(stmt, conn);
//        }
//        return null;
//    }
	public List<User> queryUserPage(int start, int size, String condition, List<Object> parmas) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			String sql = "select * from user_new where "+ condition +" limit ?,?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			for (int i =1;i <= parmas.size();i++){
				ps.setObject(i,parmas.get(i-1));
			}
			ps.setObject(parmas.size()+1,start);
			ps.setObject(parmas.size()+2,size);

			ResultSet rs = ps.executeQuery();
			List<User> list = new ArrayList<User>();
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("user_name"));
				user.setPwd(rs.getString("pwd"));
				user.setRole(rs.getInt("role"));
				user.setAccount(rs.getString("account"));
				user.setHeadimg(rs.getString("headimg"));
				if (rs.getTimestamp("create_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					user.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
				}
				list.add(user);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(ps, conn);
		}
		return null;
	}

	public User select(Integer id) {
        Connection conn = null;
        Statement stmt = null;
        try {
        	conn = getConnection();
            stmt = conn.createStatement();
            String sql = "select * from user_new where id=" + id;
            ResultSet rs = stmt.executeQuery(sql);
            User user = null;
            if(rs.next()) {
            	user = new User();
            	user.setId(rs.getInt("id"));
            	user.setUserName(rs.getString("user_name"));
            	user.setPwd(rs.getString("pwd"));
            	user.setRole(rs.getInt("role"));
            	user.setAccount(rs.getString("account"));
            	if (rs.getString("headimg") != null) {
            		user.setHeadimg("./download.do?f=" + URLEncoder.encode(rs.getString("headimg"), "utf-8"));
            	}
            }
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
            closeConnection(stmt, conn);
        }
        return null;
	}

	public int updateUser(String userName,String password,Integer role,String headImg,String account) {
		int ret = DbUtil.getInstance().execute("update user_new set user_name=?,pwd=?,role=?,headimg=? where account=?", new Object[]{userName,password,role,headImg,account});
		return ret;
	}



	public int deleteUser(Integer id) {
		int ret = DbUtil.getInstance().execute("delete from user_new where id=?", new Object[]{id});
		return ret;
	}

	public int update_img(User user,String headImageSrc) {
		String account = user.getAccount();
		int ret = DbUtil.getInstance().execute("update user_new set headimg=? where account=?", new Object[]{headImageSrc,account});
		return ret;
	}

	private Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/demo6?useUnicode=true&characterEncoding=utf8",
					"root", "123456");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	private void closeConnection(Statement stmt, Connection conn) {
		try {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
